﻿Imports System.Data.SqlClient
Imports DTO

Public Class BookingDAO
    Public Sub InsertBooking(ByVal custId As Integer, ByVal staffId As Integer, ByVal dateIn As Date, ByVal dateOut As Date)
        Try
            Dim strSQL As String = String.Format("INSERT INTO Booking (CustomerID, StaffID, DateIn, DateOut, Status) VALUES ({0}, {1}, '{2}', '{3}', 'pending')", custId, staffId, dateIn, dateOut)
            Connection.ExcuteQuery(strSQL)
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub Delete(ByVal bookingId As Integer)
        Try
            Dim sSQL As String = String.Format("DELETE FROM Booking WHERE BookingID={0} DELETE FROM BookRoom WHERE BookingID={0} ", bookingId)
            Connection.ExcuteQuery(sSQL)
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub Update(ByVal bookingId As Integer, ByVal dateIn As Date, ByVal dateOut As Date)
        Try
            Dim sSQL As String = "UPDATE Booking SET "
            If dateIn <> Nothing Then
                sSQL = String.Format("{0} DateIn='{1}' ", sSQL, dateIn)
            End If
            If dateOut <> Nothing Then
                sSQL = String.Format("{0}, DateOut='{1}'", sSQL, dateOut)
            End If

            'sSQL = String.Format("{0} ", sSQL)
            'sSQL = String.Format("{0} ", sSQL)
            sSQL = String.Format("{0} Where BookingId={1} ", sSQL, bookingId)
            Connection.ExcuteQuery(sSQL)
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            Throw ex
        End Try
    End Sub

    Public Sub UpdateStatus(ByVal bookingId As Integer, ByVal status As String)
        Try
            Dim sSQL As String = "UPDATE Booking SET "  
            sSQL = String.Format("{0} Status='{1}'", sSQL, status)

            'sSQL = String.Format("{0} ", sSQL)
            'sSQL = String.Format("{0} ", sSQL)
            sSQL = String.Format("{0} Where BookingId={1} ", sSQL, bookingId)
            Connection.ExcuteQuery(sSQL)
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            Throw ex
        End Try
    End Sub

    Public Function GetBookingIdMax() As Integer
        Dim result As Integer = 0
        Try
            Dim sql As String = String.Format("SELECT top 1 BookingID  FROM Booking order by BookingID desc")
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                result = reader("BookingID")
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return result
    End Function


    Public Function GetListBooking(ByVal staffID As Integer, ByVal passport As String) As List(Of BookingDTO)
        Dim listResult As New List(Of BookingDTO)
        Try
            Dim sql As String = "select b.BookingID, b.CustomerID, b.StaffID , b.DateIn, b.DateOut ,b.Status,"
            sql = String.Format("{0} isnull(cust.LName + cust.FName,'') CustomerName, cust.Passport FROM Booking b, Customer cust ", sql)
            sql = String.Format("{0} where b.CustomerID=cust.CustomerID and b.StaffID={1} ", sql, staffID)
            If passport > "" Then
                sql = String.Format("{0} and cust.Passport='{1}' ", sql, passport)
                'sql = String.Format("")
            End If
            sql = String.Format("{0}  order by b.DateIn desc ", sql)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim bookingDto As New BookingDTO
                bookingDto.BookingID = reader("BookingID")
                bookingDto.CustomerID = reader("CustomerID")
                bookingDto.StaffID = reader("StaffID")
                bookingDto.DateIn = reader("DateIn")
                bookingDto.DateOut = reader("DateOut")
                bookingDto.Status = reader("Status")
                bookingDto.CustomerName = reader("CustomerName")
                bookingDto.Passport = reader("Passport")

                listResult.Add(bookingDto)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function


    Public Function GetByBookingId(ByVal bookingId As Integer) As BookingDTO
        Dim result As New BookingDTO
        Try
            Dim sql As String = "select b.BookingID, b.CustomerID, b.StaffID , b.DateIn, b.DateOut ,b.Status,"
            sql = String.Format("{0} isnull(cust.LName + cust.FName,'') CustomerName, cust.Passport FROM Booking b, Customer cust ", sql)
            sql = String.Format("{0} where b.CustomerID=cust.CustomerID and b.BookingId={1} ", sql, bookingId)
          
            sql = String.Format("{0}  order by b.DateIn desc ", sql)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                result.BookingID = reader("BookingID")
                result.CustomerID = reader("CustomerID")
                result.StaffID = reader("StaffID")
                result.DateIn = reader("DateIn")
                result.DateOut = reader("DateOut")
                result.Status = reader("Status")
                result.CustomerName = reader("CustomerName")
                result.Passport = reader("Passport")

            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return result
    End Function

    Public Function GetListCustomerByBooking(ByVal staffId As Integer) As List(Of BookingDTO)
        Dim listResult As New List(Of BookingDTO)
        Try
            Dim sql As String = "select  b.BookingID, b.CustomerID, b.StaffID , b.DateIn, b.DateOut ,b.Status,"
            sql = String.Format("{0} isnull(cust.LName + cust.FName,'') CustomerName, cust.Passport FROM Booking b, Customer cust ", sql)
            sql = String.Format("{0} where b.CustomerID=cust.CustomerID and b.Status='checkin' and b.StaffID={1} ", sql, staffId)
            sql = String.Format("{0}  order by cust.LName  ", sql)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim booking As New BookingDTO
                booking.CustomerID = reader("CustomerID")            
                booking.CustomerName = reader("CustomerName")
                booking.BookingID = reader("BookingID")
                booking.StaffID = reader("StaffID")
                booking.DateIn = reader("DateIn")
                booking.DateOut = reader("DateOut")
                booking.Status = reader("Status")
                listResult.Add(booking)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function
End Class
